/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package tourmsystem.tourmanaging.dao;

import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import tourmsystem.tourmanaging.classes.Definition;
import tourmsystem.tourmanaging.classes.Tour;
import tourmsystem.tourmanaging.classes.Tour_Customer;
import tourmsystem.tourmanaging.view.TourManageFrame;

public class TourController implements ActionListener {

    private Tour tour;
    private TourManageFrame view;
    String dbClass = "com.mysql.jdbc.Driver";
    String dbUrl = "jdbc:mysql://localhost/tourmanagement";
    String userName = "root";
    String password = "";
    private ResultSet rs;
    private Connection conn;
    private PreparedStatement preSt;
    private Statement st;

    /*------constructer---------------------------------------------------------
     */
    public TourController() {
        tour = new Tour();
        view = new TourManageFrame(tour);
    }

    public TourController(Tour tour, TourManageFrame view) {
        this.tour = tour;
        this.view = view;
    }
    /* -------------------------------------------------------------------------
     * phuong thuc thiet lap ket noi voi co so du lieu
     * tra ve true neu connect thanh cong
     * false neu nguoc lai
     */

    private boolean getConnection(String dbClass, String dbUrl, String userName,
            String password) {
        conn = null;
        try {
            Class.forName(dbClass);
            conn = DriverManager.getConnection(dbUrl, userName, password);
            System.out.println("connected!");
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    /*
     * find tour by id, tra lai doi tuong tour neu tim thay, tra lai null neu ngu?c l?i
     */
    public Tour findTourById(String idString) throws Exception {
        getConnection(dbClass, dbUrl, userName, password);
        String sql = "SELECT `Id`, `name`, `startDate`, `duration`, `type`, `schedule`, `price`, "
                + "`discount`, `maxPeople`, `imgLinks` FROM `tour` WHERE id = '" + idString + "'";
        Statement st = (Statement) conn.createStatement();
        rs = null;
        rs = st.executeQuery(sql);
        if (rs.next()) {
            System.out.println("co ket qua");
            Tour t = getTourFromResultSet(rs);
            return t;
        } else {
            System.out.println("khong co ket qua");
            return null;
        }
    }
    /*phuong thuc them moi tour vao co so du lieu
    @params tour: tham so truyen vao la object
    tra ve gia tri */

    public void insertTour(Tour tour, String sql) throws Exception {
        if (!getConnection(dbClass, dbUrl, userName, password)) {
            view.showWarning("Khong the ket noi toi CSDL!");
            return;
        };
        String insertSQL = "";
        if (sql.equals("add")) {
            insertSQL = "insert into tour(id, name, startDate, duration, type,  schedule, price, discount, maxPeople, imgLinks ) value (?,?,?,?,?,?,?,?,?,?)";
        } else {
            insertSQL = sql;
        }
        try {
            preSt = (PreparedStatement) conn.prepareStatement(insertSQL);
            preSt.setString(1, tour.getId());
            // check if tourId is already existed!
            if (sql.equals("add")) {
                if (findTourById(tour.getId()) != null) {
                    throw new Exception("Mã tour dã t?n t?i, hãy nh?p mã khác!");
                }
            }
            preSt.setString(2, tour.getName());

            java.sql.Date sqlDate = new java.sql.Date(tour.getStartDate().getTime());
            System.out.println("data " + sqlDate);
            preSt.setDate(3, sqlDate);
            System.out.println("duration " + tour.getDuration());
            preSt.setInt(4, tour.getDuration());
            preSt.setInt(5, tour.getType());
            preSt.setString(6, tour.getSchedule());
            preSt.setFloat(7, tour.getPrice());
            preSt.setFloat(8, tour.getDiscount());
            preSt.setInt(9, tour.getMaxP());
            preSt.setString(10, tour.getImgLink());
            System.out.println("prest" + preSt);
            int effectedRow = preSt.executeUpdate();
            view.showWarning("Nh?p thông tin thành công!");
            if (effectedRow == 0) {
                throw new SQLException("tao thong tin bi loi");
            }
        } finally {
            conn.close();
        }
    }


    /*ham update thong tin tour
     * params:
     * tourId : ma tour
     * col : ten cot 
     * value : gia tri so sanh*/
    public void updateTour(String tourId, String col, String value) {
        Statement st = null;
        String sqlUpdate = "update tour set " + col + " = " + value + " where id = " + tourId;
        try {
            if (conn == null || conn.isClosed()) {
                getConnection(dbClass, dbUrl, userName, password);
            }
            st = (Statement) conn.createStatement();
            int eff = st.executeUpdate(sqlUpdate);
            if (eff > 0) {
                System.out.println("updated!");
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

    }

    /*   Ham xu ly su kien cua lop view
     * 
     */
    @Override
    public void actionPerformed(ActionEvent e) {
        if (e.getActionCommand().equals("Mở tour")) {

            try {
//                Lay cac tour dang mo trong bang tour_customer hien thi len bang
                ArrayList<Tour_Customer> ts = getAllOpeningTours();
//                if (tours == null) {
//                    view.showWarning("chưa có tour nào được mở");
                
//                ArrayList<Tour> ts = getAllTours("tour", "");
//                  view.openForm();
//                  view.addDataToComboBox(ts, view.CB_MO);
//                System.out.println("ts" + ts.size());
//                if(ts.size() == 0) return ;
//     
                
                
            } catch (Exception ex) {
                ex.printStackTrace();
                view.showWarning("Error " + ex.getMessage());
            }
        }
        else if(e.getActionCommand().equals("Lưu thay đổi")){
            Tour t = view.getTourInput();
            System.out.println("tour" + t);
            try {
                insertTour(t, "UPDATE `tour` SET `Id`=?,`name`=?,`startDate`=?,"
                        + "`duration`=?,`type`=?,`schedule`=?,`price`=?,`discount`=?,"
                        + "`maxPeople`=?,`imgLinks`=? WHERE id = '" + t.getId() + "'");
                view.showWarning("Saved!");
            } catch (Exception ex) {
                Logger.getLogger(TourController.class.getName()).log(Level.SEVERE, null, ex);
                view.showWarning("Can't save, error " + ex.getMessage());
            }
        }
    }

    private Tour getTourFromResultSet(ResultSet rs) throws Exception {
        if (rs == null) {
            return null;
        }
        Tour tour = new Tour();
        tour.setId(rs.getString(Definition.T_ID));
        tour.setName(rs.getString(Definition.T_NAME));
        tour.setDiscount(rs.getFloat(Definition.T_DISC));
        tour.setPrice(rs.getFloat(Definition.T_PRICE));
        tour.setImgLink(rs.getString(Definition.T_IMGL));
        tour.setMaxP(rs.getInt(Definition.T_MAXP));
        tour.setDuration(rs.getInt(Definition.T_DISC));
        tour.setSchedule(rs.getString(Definition.T_SCHEL));
        java.util.Date ud = new java.util.Date(rs.getDate(Definition.T_DATE).getTime());
        tour.setStartDate(ud);
        tour.setType(rs.getInt(Definition.T_TYPE));

        return tour;
    }
    /*
     * Lay cac tour hien co trong bang tour dang mo
     */
    public ArrayList<Tour_Customer> getAllOpeningTours() throws Exception{
        if (!getConnection(dbClass, dbUrl, userName, password)) {
            view.showWarning("Khong the ket noi toi CSDL!");
            return null;
        };
        
        ArrayList<Tour_Customer> openingTours = new ArrayList<Tour_Customer>();
        String select = "select CUSTOMER.QUANTITY, TOUR.STARTDATE,TOUR.ID from TOUR, CUSTOMER WHERE CUSTOMER.TOURID = TOUR.ID";
        
        Statement st = null;
        rs = null;
        try{
            st = (Statement) conn.createStatement();
            rs = st.executeQuery(select);
            while(rs.next()){
       System.out.println("got");
                
            }
//            if(openingTours.size() >0)
//            return openingTours;
//            return null;
        }
        finally{
            st.close();
            conn.close();
        }
        return openingTours;
    }
    /*lấy các tour hien co trong bang tour*/

    public ArrayList<Tour> getAllTours(String table, String whereClause) throws Exception {
        if (!getConnection(dbClass, dbUrl, userName, password)) {
            view.showWarning("Khong the ket noi toi CSDL!");
            return null;
        };
      
        ArrayList<Tour> tours = new ArrayList<Tour>();
        String selectSql = "select * from " + table + whereClause;
        try {
            Statement select = (Statement) conn.createStatement();
            rs = select.executeQuery(selectSql);
            while (rs.next()) {
                Tour t = getTourFromResultSet(rs);
                tours.add(t);
            }
        } finally {
            if (rs != null) {
                rs.close();
            }
            conn.close();
        }

        if (tours.size() == 0) {
            return null;
        } else {
            return tours;
        }
    }

    /*
     * Phuong thuc xoa mot tour 
     */

    public boolean deleteTour(String tableName, String colName, String keyValue) throws Exception {
        if (!getConnection(dbClass, dbUrl, userName, password)) {
            view.showWarning("Khong the ket noi toi CSDL!");
            return false;
        };
        try {
            String sql = "DELETE FROM tour WHERE " + colName + " =?";
            PreparedStatement pstmt = (PreparedStatement) conn.prepareStatement(sql);
            // Set the value
            pstmt.setString(1, keyValue);
            int deleteCount = pstmt.executeUpdate();
            if (deleteCount > 0) {
                return true;
            }
        } finally {
            conn.close();
        }
        return false;
    }

//   chen doi tuong moi vao bang thong tin tour dang mo
    public void insertTour_Cus(Tour_Customer c)throws Exception {
        if(!getConnection(dbClass, dbUrl, userName, password)){
            view.showWarning("Không thể kết nối với cơ sở dữ liệu");
            return;
        }
           preSt = null;
           rs = null;
        String sqlInsert = "INSERT INTO `tour_customer`( `tourId`, `cost`, `state`) VALUES (?,?,?)";
        try{
            preSt= (PreparedStatement) conn.prepareStatement(sqlInsert, Statement.RETURN_GENERATED_KEYS);
            preSt.setString(1, c.getTour().getId());
            preSt.setFloat(2, c.getCost());
            preSt.setString(3, c.getState());
            int eff = preSt.executeUpdate();
            if(eff == 0) throw new SQLException("Creating object failed!, no rows affected! ");
            rs = preSt.getGeneratedKeys();
            if(rs.next())
                c.setId(rs.getInt(1));
            else
            throw  new SQLException("Creating object failed!, no generated key was found ");
        }
        finally{
            conn.close();
            preSt.close();
            rs.close();
        }
    }
}
